1 Imports System.Data.SqlClient
2 Public Class frmVoucher
3 Sub fillSchool()
4 Try
5 con = New SqlConnection(cs)
6 con.Open()
7 adp = New SqlDataAdapter()
8 adp.SelectCommand = New SqlCommand("SELECT distinct (SchoolName) FROM SchoolInfo", con)
9 ds = New DataSet("ds")
10 adp.Fill(ds)
11 dtable = ds.Tables(0)
12 cmbSchoolName.Items.Clear()
13 For Each drow As DataRow In dtable.Rows
14 cmbSchoolName.Items.Add(drow(0).ToString())
15 Next
16 Catch ex As Exception
17 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
18 End Try
19 End Sub
20
21 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
22 Me.Close()
23 End Sub
24
25 Sub Reset()
26 txtVoucherID.Text = ""
27 txtName.Text = ""
28 txtDetails.Text = ""
29 txtParticulars.Text = ""
30 txtNotes.Text = ""
31 cmbSchoolName.SelectedIndex = -1
32 txtVoucherNo.Text = ""
33 txtAmount.Text = ""
34 txtGrandTotal.Text = ""
35 dtpDate.Text = Today
36 DataGridView1.Rows.Clear()
37 btnPrint.Enabled = False
38 btnSave.Enabled = True
39 btnDelete.Enabled = False
40 btnUpdate.Enabled = False
41 btnAdd.Enabled = True
42 btnRemove.Enabled = False
43 Clear()
44 auto()
45 End Sub
46
47 Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
48 Try
49 If txtParticulars.Text = "" Then
50 MessageBox.Show("Please enter particulars", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
51 txtParticulars.Focus()
52 Exit Sub
53 End If
54 If txtAmount.Text = "" Then
55 MessageBox.Show("Please enter amount", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
56 txtAmount.Focus()
57 Exit Sub
58 End If
59 If DataGridView1.Rows.Count = 0 Then
60 DataGridView1.Rows.Add(txtParticulars.Text, txtAmount.Text, txtNotes.Text)
61 Dim k As Double = 0
62 k = GrandTotal()
63 k = Math.Round(k, 2)
64 txtGrandTotal.Text = k
65 Clear()
66 Exit Sub
67 End If
68 DataGridView1.Rows.Add(txtParticulars.Text, txtAmount.Text, txtNotes.Text)
69 Dim j As Double = 0
70 j = GrandTotal()
71 j = Math.Round(j, 2)
72 txtGrandTotal.Text = j
73 Clear()
74 Catch ex As Exception
75 MsgBox(ex.Message)
76 End Try
77 End Sub
78 Sub Clear()
79 txtParticulars.Text = ""
80 txtAmount.Text = ""
81 txtNotes.Text = ""
82 End Sub
83
84 Public Function GrandTotal() As Double
85 Dim sum As Double = 0
86 Try
87 For Each r As DataGridViewRow In Me.DataGridView1.Rows
88 sum = sum + r.Cells(1).Value
89 Next
90 Catch ex As Exception
91 MsgBox(ex.Message)
92 End Try
93 Return sum
94 End Function
95
96
97 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
98 Reset()
99 Reset()
100 End Sub
101
102
103
104 Private Sub auto()
105 Dim Num As Integer = 0
106 con = New SqlConnection(cs)
107 con.Open()
108 Dim sql As String = ("SELECT MAX(ID) from Voucher")
109 cmd = New SqlCommand(sql)
110 cmd.Connection = con
111 If (IsDBNull(cmd.ExecuteScalar)) Then
112 Num = 1
113 txtVoucherID.Text = Num.ToString
114 txtVoucherNo.Text = "V-" + Num.ToString
115 Else
116 Num = cmd.ExecuteScalar + 1
117 txtVoucherID.Text = Num.ToString
118 txtVoucherNo.Text = "V-" + Num.ToString
119 End If
120 cmd.Dispose()
121 con.Close()
122 con.Dispose()
123 End Sub
124
125 Sub Print()
126 Try
127 Cursor = Cursors.WaitCursor
128 Timer1.Enabled = True
129 Dim rpt As New rptVoucher 'The report you created.
130 Dim myConnection As SqlConnection
131 Dim MyCommand As New SqlCommand()
132 Dim myDA As New SqlDataAdapter()
133 Dim myDS As New DataSet 'The DataSet you created.
134 myConnection = New SqlConnection(cs)
135 MyCommand.Connection = myConnection
136 MyCommand.CommandText = "SELECT Voucher.ID, Voucher.VoucherNo, Voucher.Date, Voucher.Name, Voucher.Details, Voucher.SchoolID, Voucher.GrandTotal, Voucher_OtherDetails.VD_ID, Voucher_OtherDetails.VoucherID,Voucher_OtherDetails.Particulars, Voucher_OtherDetails.Amount, Voucher_OtherDetails.Note, SchoolInfo.S_Id, SchoolInfo.SchoolName, SchoolInfo.Address, SchoolInfo.ContactNo, SchoolInfo.AltContactNo,SchoolInfo.FaxNo, SchoolInfo.Email, SchoolInfo.Website, SchoolInfo.Logo, SchoolInfo.RegistrationNo, SchoolInfo.DiseNo, SchoolInfo.IndexNo, SchoolInfo.EstablishedYear, SchoolInfo.Class,SchoolInfo.SchoolType FROM Voucher INNER JOIN Voucher_OtherDetails ON Voucher.ID = Voucher_OtherDetails.VoucherID INNER JOIN SchoolInfo ON Voucher.SchoolID = SchoolInfo.S_Id where VoucherNo='" & txtVoucherNo.Text & "'"
137 MyCommand.CommandType = CommandType.Text
138 myDA.SelectCommand = MyCommand
139 myDA.Fill(myDS, "Voucher")
140 myDA.Fill(myDS, "Voucher_OtherDetails")
141 myDA.Fill(myDS, "SchoolInfo")
142 rpt.SetDataSource(myDS)
143 frmReport.CrystalReportViewer1.ReportSource = rpt
144 frmReport.ShowDialog()
145 Catch ex As Exception
146 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
147 End Try
148 End Sub
149 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
150 Try
151 If Len(Trim(cmbSchoolName.Text)) = 0 Then
152 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
153 cmbSchoolName.Focus()
154 Exit Sub
155 End If
156 If DataGridView1.Rows.Count = 0 Then
157 MessageBox.Show("sorry no data added to grid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
158 Exit Sub
159 End If
160 con = New SqlConnection(cs)
161 con.Open()
162 Dim cb As String = "insert into Voucher( Id, VoucherNo, Date,Name,Details,schoolID,GrandTotal) Values (@d1,@d2,@d3,@d4,@d5,@d6,@d7)"
163 cmd = New SqlCommand(cb)
164 cmd.Parameters.AddWithValue("@d1", txtVoucherID.Text)
165 cmd.Parameters.AddWithValue("@d2", txtVoucherNo.Text)
166 cmd.Parameters.AddWithValue("@d3", dtpDate.Text)
167 cmd.Parameters.AddWithValue("@d4", txtName.Text)
168 cmd.Parameters.AddWithValue("@d5", txtDetails.Text)
169 cmd.Parameters.AddWithValue("@d6", txtSchoolID.Text)
170 cmd.Parameters.AddWithValue("@d7", txtGrandTotal.Text)
171 cmd.Connection = con
172 cmd.ExecuteReader()
173 con.Close()
174 con = New SqlConnection(cs)
175 con.Open()
176 Dim cb1 As String = "insert into Voucher_OtherDetails(VoucherID,Particulars,Amount,Note) VALUES (" & txtVoucherID.Text & ",@d1,@d2,@d3)"
177 cmd = New SqlCommand(cb1)
178 cmd.Connection = con
179 ' Prepare command for repeated execution
180 cmd.Prepare()
181 ' Data to be inserted
182 For Each row As DataGridViewRow In DataGridView1.Rows
183 If Not row.IsNewRow Then
184 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value)
185 cmd.Parameters.AddWithValue("@d2", row.Cells(1).Value)
186 cmd.Parameters.AddWithValue("@d3", row.Cells(2).Value)
187 cmd.ExecuteNonQuery()
188 cmd.Parameters.Clear()
189 End If
190 Next
191 con.Close()
192 Dim st As String = "added the new voucher having voucher no.'" & txtVoucherNo.Text & "'"
193 LogFunc(lblUser.Text, st)
194 btnSave.Enabled = False
195 MessageBox.Show("Successfully saved", "Voucher", MessageBoxButtons.OK, MessageBoxIcon.Information)
196 Print()
197 Catch ex As Exception
198 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
199 End Try
200 End Sub
201 Public Sub DeleteRecord()
202 Try
203 Dim RowsAffected As Integer = 0
204 con = New SqlConnection(cs)
205 con.Open()
206 Dim ct As String = "delete from Voucher where ID=" & txtVoucherID.Text & ""
207 cmd = New SqlCommand(ct)
208 cmd.Connection = con
209 RowsAffected = cmd.ExecuteNonQuery()
210 If con.State = ConnectionState.Open Then
211 con.Close()
212 End If
213 If RowsAffected > 0 Then
214 Dim st As String = "deleted the voucher having voucher no.'" & txtVoucherNo.Text & "'"
215 LogFunc(lblUser.Text, st)
216 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
217 Reset()
218 Reset()
219 Else
220 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
221 Reset()
222 End If
223 con.Close()
224 Catch ex As Exception
225 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
226 End Try
227 End Sub
228
229 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
230 Try
231 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
232 DeleteRecord()
233 End If
234 Catch ex As Exception
235 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
236 End Try
237 End Sub
238
239 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
240 Try
241 If Len(Trim(cmbSchoolName.Text)) = 0 Then
242 MessageBox.Show("Please select school name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
243 cmbSchoolName.Focus()
244 Exit Sub
245 End If
246 If DataGridView1.Rows.Count = 0 Then
247 MessageBox.Show("sorry no data added to grid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
248 Exit Sub
249 End If
250 con = New SqlConnection(cs)
251 con.Open()
252 Dim cb As String = "Update Voucher set VoucherNo=@d2, Date=@d3,Name=@d4,Details=@d5,schoolID=@d6,GrandTotal=@d7 where ID=@d1"
253 cmd = New SqlCommand(cb)
254 cmd.Parameters.AddWithValue("@d1", txtVoucherID.Text)
255 cmd.Parameters.AddWithValue("@d2", txtVoucherNo.Text)
256 cmd.Parameters.AddWithValue("@d3", dtpDate.Text)
257 cmd.Parameters.AddWithValue("@d4", txtName.Text)
258 cmd.Parameters.AddWithValue("@d5", txtDetails.Text)
259 cmd.Parameters.AddWithValue("@d6", txtSchoolID.Text)
260 cmd.Parameters.AddWithValue("@d7", txtGrandTotal.Text)
261 cmd.Connection = con
262 cmd.ExecuteReader()
263 con.Close()
264 con = New SqlConnection(cs)
265 con.Open()
266 Dim ct As String = "delete from Voucher_OtherDetails where VoucherID=" & txtVoucherID.Text & ""
267 cmd = New SqlCommand(ct)
268 cmd.Connection = con
269 cmd.ExecuteNonQuery()
270 con.Close()
271 con = New SqlConnection(cs)
272 con.Open()
273 Dim cb1 As String = "insert into Voucher_OtherDetails(VoucherID,Particulars,Amount,Note) VALUES (" & txtVoucherID.Text & ",@d1,@d2,@d3)"
274 cmd = New SqlCommand(cb1)
275 cmd.Connection = con
276 ' Prepare command for repeated execution
277 cmd.Prepare()
278 ' Data to be inserted
279 For Each row As DataGridViewRow In DataGridView1.Rows
280 If Not row.IsNewRow Then
281 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value)
282 cmd.Parameters.AddWithValue("@d2", row.Cells(1).Value)
283 cmd.Parameters.AddWithValue("@d3", row.Cells(2).Value)
284 cmd.ExecuteNonQuery()
285 cmd.Parameters.Clear()
286 End If
287 Next
288 con.Close()
289 Dim st As String = "updated the voucher having voucher no.'" & txtVoucherNo.Text & "'"
290 LogFunc(lblUser.Text, st)
291 btnUpdate.Enabled = False
292 MessageBox.Show("Successfully updated", "Voucher", MessageBoxButtons.OK, MessageBoxIcon.Information)
293 Catch ex As Exception
294 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
295 End Try
296 End Sub
297
298
299 Private Sub DataGridView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
300 btnRemove.Enabled = True
301 End Sub
302
303
304 Private Sub btnRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemove.Click
305 Try
306 For Each row As DataGridViewRow In DataGridView1.SelectedRows
307 DataGridView1.Rows.Remove(row)
308 Next
309 Dim k As Double = 0
310 k = GrandTotal()
311 k = Math.Round(k, 2)
312 txtGrandTotal.Text = k
313 btnRemove.Enabled = False
314 Catch ex As Exception
315 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
316 End Try
317 End Sub
318
319 Private Sub btnPrint_Click(sender As Object, e As EventArgs) Handles btnPrint.Click
320 Print()
321 End Sub
322
323 Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
324 Cursor = Cursors.Default
325 Timer1.Enabled = False
326 End Sub
327
328 Private Sub txtAmount_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtAmount.KeyPress
329 Dim keyChar = e.KeyChar
330
331 If Char.IsControl(keyChar) Then
332 'Allow all control characters.
333 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
334 Dim text = Me.txtAmount.Text
335 Dim selectionStart = Me.txtAmount.SelectionStart
336 Dim selectionLength = Me.txtAmount.SelectionLength
337
338 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
339
340 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
341 'Reject an integer that is longer than 16 digits.
342 e.Handled = True
343 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
344 'Reject a real number with two many decimal places.
345 e.Handled = False
346 End If
347 Else
348 'Reject all other characters.
349 e.Handled = True
350 End If
351 End Sub
352
353 Private Sub frmVoucher_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
354 fillSchool()
355 End Sub
356
357 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
358 frmVoucherRecord.Reset()
359 frmVoucherRecord.ShowDialog()
360 End Sub
361
362 Private Sub cmbSchoolName_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSchoolName.SelectedIndexChanged
363 Try
364 con = New SqlConnection(cs)
365 con.Open()
366 cmd = con.CreateCommand()
367 cmd.CommandText = "SELECT S_ID FROM SchoolInfo where SchoolName=@d1"
368 cmd.Parameters.AddWithValue("@d1", cmbSchoolName.Text)
369 rdr = cmd.ExecuteReader()
370 If rdr.Read() Then
371 txtSchoolID.Text = rdr.GetValue(0)
372 End If
373 If (rdr IsNot Nothing) Then
374 rdr.Close()
375 End If
376 If con.State = ConnectionState.Open Then
377 con.Close()
378 End If
379 Catch ex As Exception
380 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
381 End Try
382 End Sub
383 End Class
